<?php
	session_start();
	ob_start();

	require("./inc/config-system.inc.php");
	require("./inc/config-db.inc.php");
	require("./inc/fm.inc.php");
	require("./class/adodb/adodb.inc.php");

	// connection database
	$objConn = ADONewConnection(DB_DRIVER);
	$objConn->Connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>แสดงรายการ GL</title>
<style type="text/css">
<!--
.center {
	text-align: center;
	font-weight: normal;
	font-size: 15px;
	font-family: "Microsoft Sans Serif";
}
.right {
	text-align: right;
}
.body {
	font-family: "Microsoft Sans Serif";
	font-size: 15px;
	margin-top: 0px;
}
.input {
	font-family: "Microsoft Sans Serif";
	font-size: 15px;
}

.numeric {
	font-family: "Microsoft Sans Serif";
	font-size: 15px;
}

.header_center {
	font-family: "Microsoft Sans Serif";
	font-size: 16px;
	text-decoration: underline;
	text-align: center;
	font-weight: bold;
	color: #FFF;
	background-color: #666;
}
.input_disable {
	font-family: "Microsoft Sans Serif";
	font-size: 15px;
	color: #000;
	background-color: #CCC;
}
.input_focus {
	font-family: "Microsoft Sans Serif";
	font-size: 15px;
	background-color: #FFC;
	border: 2px solid #F60;
}
.sum {
	font-size: 15px;
}
.subtot {
	background-color:#ffffcc !important;
	text-align: right;
	font-weight: bold;
}
.grandtot {
	background-color:#ccffcc !important;
	text-align: right;
	font-weight: bold;
}
table.tablesorter {
	font-size: 15px !important;
}
table.tablesorter tbody td.right {
	font-size: 15px;
	text-align: right;
}
input[type="submit"], input[type="reset"], input[type="button"] {
	position: relative;
	vertical-align: top;
	width: 10%;
	height: 50px;
	padding: 0;
	font-size: 20px;
	color: white;
	text-align: center;
	text-shadow: 0 1px 2px rgba(0, 0, 0, 0.25);
	background: #2ecc71;
	border: 0;
	border-bottom: 2px solid #28be68;
	cursor: pointer;
	-webkit-box-shadow: inset 0 -2px #28be68;
	box-shadow: inset 0 -2px #28be68;
}
input[name="BTTexport"] {
	width: 100%;
}
input[type="submit"]:active, input[type="reset"]:active, input[type="button"]:active {
	top: 1px;
	outline: none;
	-webkit-box-shadow: none;
	box-shadow: none;
}
input[type="text"]  {
	padding: 5px;
	border-radius: 3px;
	border: 1px solid #CCC;
	background-image: none;
}
input[type="text"]:focus {
	outline-color: #54B4AD;
}
-->
</style>

<link href="./css/datepicker.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/tablesorter.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/tablehover.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/slidepanel.css" rel="stylesheet" type="text/css" media="screen" />
<link href="./css/autocomplete.css" rel="stylesheet" type="text/css" media="screen" />

<script type="text/javascript" src="./js/jquery_1.8.3.inc.js"></script>
<script type="text/javascript" src="./js/form.inc.js"></script>
<script type="text/javascript" src="./js/datepicker.inc.js"></script>
<script type="text/javascript" src="./js/tablesorter.inc.js"></script>
<script type="text/javascript" src="./js/tablehover.inc.js"></script>
<script type="text/javascript" src="./js/livequery.inc.js"></script>
<script type="text/javascript" src="./js/autocomplete.inc.js"></script>
<script type="text/javascript" src="./js/floatingbox.inc.js"></script>

<?php require("./gl.inc.js.php"); ?>
</head>

<body class="body">

<div id="mainPanel">
    <div id="panel"><?php require("./menu.inc.php"); ?></div>
    <p class="slide"><a href="#" class="btn-slide">Main Menu</a></p>
</div>

<div class="top" id="top"><a href="#inputbox">ไปด้านบนสุด</a></div>

<br /><br />

<?php
	// get filter from user input
	if (isset($_POST["TXTgl_account"])) {
		$gl_account = $_POST["TXTgl_account"];
	} else {
		$gl_account = "";
	}

	if (isset($_POST["TXTdoc_date_from"])) {
		$doc_date_from = $_POST["TXTdoc_date_from"];
	} else {
		$doc_date_from = "";
	}

	if (isset($_POST["TXTdoc_date_to"])) {
		$doc_date_to = $_POST["TXTdoc_date_to"];
	} else {
		$doc_date_to = "";
	}
?>
<form id="gl_display" method="post" action="./gl_display4.php">

    <table width="100%" border="0" cellpadding="5" cellspacing="5" class="body">

        <tr class="header_center">
            <td><a name="inputbox"><h1>เงื่อนไขการเลือกข้อมูล รายงานบัญชี เงินทดรองจ่าย</h1></a></td>
        </tr>
        <tr>
            <td class="left">&nbsp;</td>
        </tr>
<!--    <tr>
            <td>บัญชี :<br />
			<input name="TXTgl_account" type="text" class="input" id="TXTgl_account" value="<?php echo $gl_account; ?>" /></td>
-->
		</tr>
        <tr>
            <td>วันที่ :<br />
            <input name="TXTdoc_date_from" type="text" class="input" id="TXTdoc_date_from" value="<?php echo $doc_date_from; ?>" readonly="readonly" />
            ถึง&nbsp;
            <input name="TXTdoc_date_to" type="text" class="input" id="TXTdoc_date_to" value="<?php echo $doc_date_to; ?>" readonly="readonly" /></td>
		</tr>
        <tr>
            <td><input type="submit" name="BTTexecute" id="BTTexecute" value="แสดงรายงาน" class="input" />
                <input type="reset" name="BTTreset" id="BTTreset" value="ล้าง" class="input" />
            </td>
        </tr>

	</table>

</form>

</br>

<?php if (isset($_POST["BTTexecute"])) { ?>

	<?php
		// replace * to % for use in SQL
		if ($gl_account == "") {
			$gl_account = "%";
		} else {
			$gl_account = str_replace("*", "%", $gl_account);
		}

		if ($doc_date_from == "") {
			$doc_date_from = "0000-00-00";
		} else {
			$doc_date_from = str_replace("*", "%", $doc_date_from);
		}

		if ($doc_date_to == "") {
			$doc_date_to = "9999-12-31";
		} else {
			$doc_date_to = str_replace("*", "%", $doc_date_to);
		}

		//-----> keep ADJUST data into result array
		$sum_grand_total = 0.00;
		$result_data = array();
		$docdate_temp = "";

		$sqlCommand = "SELECT * FROM tbl_adj WHERE adj_account_desc LIKE '" . $gl_account . "' AND (adj_docdate BETWEEN '" . $doc_date_from . "' AND '" . $doc_date_to . "') AND adj_type = 'ADJ2' AND deleted = 0 ORDER BY adj_account_desc";
		$recordset = $objConn->Execute($sqlCommand);

		while (!$recordset->EOF) {

			if (substr($recordset->fields["adj_payer_new"], 0, 1) == 'C') {

				$sqlCommand = "SELECT * FROM tbl_customer WHERE payer_code = '" . $recordset->fields["adj_payer_new"] . "'";
				$recordsetPAYER = $objConn->Execute($sqlCommand);

			} elseif (substr($recordset->fields["adj_payer_new"], 0, 1) == 'V') {

				$sqlCommand = "SELECT * FROM tbl_vendor WHERE payer_code = '" . $recordset->fields["adj_payer_new"] . "'";
				$recordsetPAYER = $objConn->Execute($sqlCommand);

			}

			$result_data_temp["docdate"] = $recordset->fields["adj_docdate"];
			$result_data_temp["docno"] = "AD" . str_pad($recordset->fields["adj_docno"], 8, 0, STR_PAD_LEFT);
			$result_data_temp["gl_account"] = $recordset->fields["adj_account_desc"];
			$result_data_temp["description"] = $recordset->fields["adj_description"];
			$result_data_temp["payer"] = $recordsetPAYER->fields["payer_name"];
			$result_data_temp["remark"] = $recordset->fields["adj_remark"];
			$result_data_temp["quantity"] = $recordset->fields["adj_quantity"];
			$result_data_temp["price_unit"] = $recordset->fields["adj_price_unit"];
			$result_data_temp["amount"] = ( $recordset->fields["adj_amount_loan"] + $recordset->fields["adj_amount_advance"] ) * -1;
			//$result_data_temp["amount_loan"] = $recordset->fields["adj_amount_loan"];
			//$result_data_temp["amount_advance"] = $recordset->fields["adj_amount_advance"];

			array_push($result_data, $result_data_temp);
			$recordset->MoveNext();

		}

		unset($result_data_temp); // clear array

		//-----> keep AR data into result array
		$sqlCommand = "SELECT * FROM tbl_ar WHERE ar_account_desc = '1302' AND (ar_docdate BETWEEN '" . $doc_date_from . "' AND '" . $doc_date_to . "') AND deleted = 0 ORDER BY ar_account_desc";
		$recordset = $objConn->Execute($sqlCommand);

		while (!$recordset->EOF) {

			if (substr($recordset->fields["ar_payer_new"], 0, 1) == 'C') {

				$sqlCommand = "SELECT * FROM tbl_customer WHERE payer_code = '" . $recordset->fields["ar_payer_new"] . "'";
				$recordsetPAYER = $objConn->Execute($sqlCommand);

			} elseif (substr($recordset->fields["ar_payer_new"], 0, 1) == 'V') {

				$sqlCommand = "SELECT * FROM tbl_vendor WHERE payer_code = '" . $recordset->fields["ar_payer_new"] . "'";
				$recordsetPAYER = $objConn->Execute($sqlCommand);

			}

			$result_data_temp["docdate"] = $recordset->fields["ar_docdate"];
			$result_data_temp["docno"] = "AR" . str_pad($recordset->fields["ar_docno"], 8, 0, STR_PAD_LEFT);
			$result_data_temp["gl_account"] = $recordset->fields["ar_account_desc"];
			$result_data_temp["description"] = $recordset->fields["ar_description"];
			$result_data_temp["payer"] = $recordsetPAYER->fields["payer_name"];
			$result_data_temp["remark"] = $recordset->fields["ar_remark"];
			$result_data_temp["quantity"] = $recordset->fields["ar_quantity"];
			$result_data_temp["price_unit"] = $recordset->fields["ar_price_unit"];
			$result_data_temp["amount"] = ( $recordset->fields["ar_amount_cash"] + $recordset->fields["ar_amount_saving"] + $recordset->fields["ar_amount_current"] + $recordset->fields["ar_amount_pledge"] ) * -1;

			array_push($result_data, $result_data_temp);
			$recordset->MoveNext();

		}

		unset($result_data_temp); // clear array

		//-----> keep AP data into result array
		$sqlCommand = "SELECT * FROM tbl_ap WHERE ap_account_desc = '1302' AND (ap_docdate BETWEEN '" . $doc_date_from . "' AND '" . $doc_date_to . "') AND deleted = 0 ORDER BY ap_account_desc";
		$recordset = $objConn->Execute($sqlCommand);

		while (!$recordset->EOF) {

			if (substr($recordset->fields["ap_payer_new"], 0, 1) == 'C') {

				$sqlCommand = "SELECT * FROM tbl_customer WHERE payer_code = '" . $recordset->fields["ap_payer_new"] . "'";
				$recordsetPAYER = $objConn->Execute($sqlCommand);

			} elseif (substr($recordset->fields["ap_payer_new"], 0, 1) == 'V') {

				$sqlCommand = "SELECT * FROM tbl_vendor WHERE payer_code = '" . $recordset->fields["ap_payer_new"] . "'";
				$recordsetPAYER = $objConn->Execute($sqlCommand);

			}

			$result_data_temp["docdate"] = $recordset->fields["ap_docdate"];
			$result_data_temp["docno"] = "AP" . str_pad($recordset->fields["ap_docno"], 8, 0, STR_PAD_LEFT);
			$result_data_temp["gl_account"] = $recordset->fields["ap_account_desc"];
			$result_data_temp["description"] = $recordset->fields["ap_description"];
			$result_data_temp["payer"] = $recordsetPAYER->fields["payer_name"];
			$result_data_temp["remark"] = $recordset->fields["ap_remark"];
			$result_data_temp["quantity"] = $recordset->fields["ap_quantity"];
			$result_data_temp["price_unit"] = $recordset->fields["ap_price_unit"];
			$result_data_temp["amount"] = $recordset->fields["ap_amount_cash"] + $recordset->fields["ap_amount_saving"] + $recordset->fields["ap_amount_current"] + $recordset->fields["ap_amount_pledge"];

			array_push($result_data, $result_data_temp);
			$recordset->MoveNext();

		}

		unset($result_data_temp); // clear array

		// sort result array by value
		asort($result_data);
	?>

 	<table width="100%">
        <tr class="header_center">
            <td colspan="7" height="30">รายงานบัญชี เงินทดรองจ่าย  ( แยกตามวันที่ )</td>
        </tr>
    </table>

    <table id="mainTable" width="100%">
        <tr>
        	<td>
			<?php
                $sum_sub_total = 0.00;
                $sign_string_amount = "";
            	$i = 1;

                while (list($key, $value) = each($result_data)) { // !!! do not use foreach() !!!

                    if ($value["docdate"] != $docdate_temp) {
            ?>
                <table id="myTable" name="nameTable" class="tablesorter" width="100%" border="0" cellpadding="5" cellspacing="5">

                    <thead class="input_disable">
                        <tr>
                            <!--<th width="13%">บัญชี</th>-->
                            <th width="7%">เลขที่เอกสาร</th>
                            <th width="6%">วันที่</th>
                            <th width="21%">รายการ</th>
                            <th width="11%">ชื่อผู้ชำระเงิน</th>
                            <th width="17%">หมายเหตุ</th>
                            <th width="4%">จำนวน</th>
                            <th width="6%">ราคา/หน่วย</th>
                            <th width="14%">จำนวนเงิน</th>
                            <th width="14%">&nbsp;</th>
                        </tr>
                    </thead>

                    <tbody>
					<?php
                            $docdate_temp = $value["docdate"];
                            $sum_total = 0.00;
							//$sum_sub_loan = 0.00;
							//$sum_sub_advance = 0.00;
                    }

					$sum_total = $sum_total + $value["amount"];
					$sum_grand_total = $sum_grand_total + $value["amount"];
                    //$sign_string_amount = "<font color=green>" . number_format($value["amount"], 2) . "</font>";
                    //$sum_sub_loan = $sum_sub_loan + $value["amount_loan"];
                    //$sum_sub_advance = $sum_sub_advance + $value["amount_advance"];

                    ?>
                       <tr>
                            <!--<td><?php //echo $value["gl_account"]; ?></td>-->
                            <td><?php echo $value["docno"]; ?></td>
                            <td><?php echo $value["docdate"]; ?></td>
                            <td><?php echo $value["description"]; ?></td>
                            <td><?php echo $value["payer"]; ?></td>
                            <td><?php echo $value["remark"]; ?></td>
                            <td class="right"><?php echo number_format($value["quantity"], 2); ?></td>
                         	<td class="right"><?php echo number_format($value["price_unit"], 2); ?></td>
                            <td class="right"><?php echo number_format($value["amount"], 2); ?></td>
                            <td><?php //echo number_format($value["amount_advance"], 2); ?></td>
                      </tr>

					<?php
                            // read next record array for check end of gl account
                            $next_record = current($result_data); // ห้ามใช้ next() เพราะว่า cursor เลื่อนมาที่แถวต่อไปแล้วใช้ current() ดึงออกมาได้เลย

                            if ($next_record["docdate"] != $value["docdate"]) {
                    ?>
                       <tr>
                            <!--<td><?php //echo $value["gl_account"]; ?></td>-->
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                         	<td>&nbsp;</td>
                            <td class="subtot"><?php echo number_format($sum_total, 2); ?></td>
                            <td class="subtot"><?php //echo number_format($sum_sub_advance, 2); ?></td>
                      </tr>

                    <?php
						if ($i == count($result_data)) {
							$i = 1;
					?>
                       <tr><td>&nbsp;</td></tr>
                       <tr>
                            <!--<td><?php //echo $value["gl_account"]; ?></td>-->
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                            <td>&nbsp;</td>
                         	<td>&nbsp;</td>
                            <td class="grandtot"><?php echo number_format($sum_grand_total, 2); ?></td>
                            <td class="grandtot">&nbsp;</td>
                      </tr>
                    <?php
						}
					?>
                    </tbody>

				</table>

    <?php 		} ?>

    <?php 	$i = $i + 1; } // while ?>

<?php 	} // if isset($_POST["BTTexecute"] ?>

</td></tr></table>

<?php if (isset($_POST["BTTexecute"])) { ?>

    <form action="./export_gl_2_excel.php" method="post">

    <table width="100%" border="0" cellpadding="2" cellspacing="2">
        <tr>
            <td width="15%">
                <input name="BTTexport" type="submit" class="input" id="BTTexport" value="ส่งออกเป็นไฟล์ EXCEL" />
                <input name="HIDdata2excel2" type="hidden" id="HIDdata2excel2" />
            </td>
            <td width="46%">&nbsp;</td>
            <td width="18%">&nbsp;</td>
            <td width="21%">&nbsp;</td>
        </tr>
    </table>

    </form>

<?php } ?>

</body>
</html>

<?php ob_end_flush(); ?>
